﻿using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Specialized;
using System.Data;
using System.IO;

namespace TurtleEngine
{
	 /// <summary>
	 ///
	 /// </summary>
	 public class ViewsHelper
	 {
		  /// <summary>
		  /// The views
		  /// </summary>
		  private static DataTable views = new DataTable("Views");

		  /// <summary>
		  /// Gets the views.
		  /// </summary>
		  /// <value>
		  /// The views.
		  /// </value>
		  public static DataTable Views
		  {
				get { return views; }
		  }

		  /// <summary>
		  /// Gets the views.
		  /// </summary>
		  public static void GetViews()
		  {
				views.Clear();
				views = Utility.DBConnection.GetSchema("Views");

				//Utility.PrintDatatable(views);
				//Utility.WriteXML(views, views.TableName + ".xml");

				JoinViewDescription();

				//JoinViewColumnsDescription(); // no need, as we are using the already existing columns fecthed for tables.
				GetViewScript(Utility.DatabaseName);
		  }

		  /// <summary>
		  /// Writes the views.
		  /// </summary>
		  public static void WriteViews()
		  {
				WriteViewList();
				WriteViewDetails();
		  }

		  /// <summary>
		  /// Gets the view script.
		  /// </summary>
		  /// <param name="database">The database.</param>
		  private static void GetViewScript(string database)
		  {
				views.Columns.Add("TABLE_SCRIPT", typeof(String));

				var serverConnection = new ServerConnection(Utility.DBConnection);
				var server = new Server(serverConnection);
				Database db = server.Databases[database];

				var so = new ScriptingOptions();
				so.ChangeTracking = true;
				so.ClusteredIndexes = true;
				so.ExtendedProperties = true;

				foreach (View view in db.Views)
				{
					 if (view.Owner.ToLower().Equals("dbo"))
					 {
						  StringCollection script = view.Script(so);
						  var scriptArray = new string[script.Count];
						  script.CopyTo(scriptArray, 0);

						  DataRow tableRow = (views.Select("TABLE_NAME = '" + view.Name + "'"))[0];
						  for (int i = 0; i < scriptArray.Length; i++)

								//scriptArray[i] = Utility.SplitString(scriptArray[i], 150);
								scriptArray[i] = scriptArray[i].Replace("@level0type", "\n\t@level0type");

						  tableRow["TABLE_SCRIPT"] = string.Join(Environment.NewLine, scriptArray);
					 }
				}
		  }

		  /// <summary>
		  /// Joins the view description.
		  /// </summary>
		  private static void JoinViewDescription()
		  {
				views.Columns.Add("TABLE_DESCRIPTION", typeof(String));

				foreach (DataRow row in views.Rows)
				{
					 DataRow[] rows = (TablesHelper.TableDescription.Select("OBJNAME = '" + row["TABLE_NAME"] + "'"));

					 if (rows.Length > 0)
						  row["TABLE_DESCRIPTION"] = rows[0]["VALUE"];
				}
		  }

		  /// <summary>
		  /// Writes the view details.
		  /// </summary>
		  private static void WriteViewDetails()
		  {
				string xmlfile = string.Empty;
				string xslFile = string.Empty;
				string htmFile = string.Empty;

				var ds = new DataSet();

				#region "Define DataTable for ViewProperties"

				var tableProperties = new DataTable("TableProperties");
				tableProperties.Columns.Add("NAME", typeof(String));
				tableProperties.Columns.Add("VALUE", typeof(String));
				ds.Tables.Add(tableProperties);

				#endregion "Define DataTable for ViewProperties"

				#region "Define DataTable for Resultset"

				DataTable tableColumns = ColumnsHelper.Columns.Clone();
				ds.Tables.Add(tableColumns);

				#endregion "Define DataTable for Resultset"

				#region "Define DataTable for Indexes"

				DataTable tableIndexes = IndexesHelper.Indexes.Clone();
				ds.Tables.Add(tableIndexes);

				#endregion "Define DataTable for Indexes"

				#region "Define DataTable for SQL"

				var tableSQL = new DataTable("SQL");
				tableSQL.Columns.Add("CreationScript", typeof(String));
				ds.Tables.Add(tableSQL);

				#endregion "Define DataTable for SQL"

				foreach (DataRow row in views.Rows)
				{
					 string tableName = row["TABLE_NAME"].ToString();
					 xmlfile = tableName + ".xml";
					 xslFile = "ViewDetails.xsl";
					 htmFile = tableName + ".htm";

					 #region "Fill TableProperties"

					 DataRow dr = tableProperties.NewRow();
					 dr["NAME"] = "Description";
					 dr["VALUE"] = row["TABLE_DESCRIPTION"].ToString();
					 tableProperties.Rows.Add(dr);

					 dr = tableProperties.NewRow();
					 dr["NAME"] = "Name";
					 dr["VALUE"] = tableName;
					 tableProperties.Rows.Add(dr);

					 dr = tableProperties.NewRow();
					 dr["NAME"] = "Schema";
					 dr["VALUE"] = row["TABLE_SCHEMA"].ToString();
					 tableProperties.Rows.Add(dr);

					 #endregion "Fill TableProperties"

					 #region "Fill Columns"

					 string cloumnTable = string.Empty;
					 DataRow[] colRows = (ColumnsHelper.Columns.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
					 foreach (DataRow tempDr in colRows)
					 {
						  // TODO: currently, adding description to the views columns is buggy inplementation
						  DataRow[] viewRows =
							  (ViewColumnsHelper.ViewColumns.Select("VIEW_NAME = '" + row["TABLE_NAME"] + "' AND COLUMN_NAME = '" +
																  tempDr["COLUMN_NAME"] + "'"));
						  if (viewRows.Length > 0)
								cloumnTable = viewRows[0]["TABLE_NAME"].ToString();
						  DataRow[] rows =
							  (ColumnsHelper.Columns.Select("TABLE_NAME = '" + cloumnTable + "' AND COLUMN_NAME = '" + tempDr["COLUMN_NAME"] +
														  "'"));
						  if (rows.Length > 0)
								tempDr["COLUMN_DESCRIPTION"] = rows[0]["COLUMN_DESCRIPTION"];
						  tableColumns.ImportRow(tempDr);
					 }

					 #endregion "Fill Columns"

					 #region "Fill Indexes"

					 DataRow[] indexRows = (IndexesHelper.Indexes.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
					 foreach (DataRow tempDr in indexRows)
					 {
						  tableIndexes.ImportRow(tempDr);
					 }

					 #endregion "Fill Indexes"

					 #region "SQL"

					 DataRow[] sqlRows = (views.Select("TABLE_NAME = '" + row["TABLE_NAME"] + "'"));
					 foreach (DataRow tempDr in sqlRows)
					 {
						  DataRow sql = tableSQL.NewRow();
						  sql["CreationScript"] = tempDr["TABLE_SCRIPT"];
						  tableSQL.Rows.Add(sql);
					 }

					 #endregion "SQL"

					 File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());
					 var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
					 ht.TableTransformer();

					 tableProperties.Clear();
					 tableColumns.Clear();
					 tableIndexes.Clear();
					 tableSQL.Clear();
				}
		  }

		  /// <summary>
		  /// Writes the view list.
		  /// </summary>
		  private static void WriteViewList()
		  {
				string xmlfile = "ViewList.xml";
				string xslFile = "ViewList.xsl";
				string htmFile = "ViewList.htm";

				var ds = new DataSet("ViewList");
				ds.Tables.Add(views);

				File.WriteAllText(@"xml\" + xmlfile, ds.GetXml());

				var ht = new HTMLTransfomer(xmlfile, xslFile, htmFile);
				ht.TableTransformer();
		  }
	 }
}